<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*"%>
				<%@ page import="javax.sql.rowset.CachedRowSet,com.sun.rowset.CachedRowSetImpl;" %>
				<%
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");

						String action = request.getParameter("action");
						// Check if an insertion is requested
						if (action != null && action.equals("insert")) {
							// Begin transaction
							conn.setAutoCommit(false);
							//insert section
							PreparedStatement pstmt = conn.prepareStatement(
					                "INSERT INTO review_session VALUES (?, ?)");
							pstmt.setInt(1, Integer.parseInt(request.getParameter("section_id")));   
							pstmt.setString(2, request.getParameter("date_time")); 	
								
					        pstmt.executeUpdate();  

							// Commit transaction
							conn.commit();
							conn.setAutoCommit(true);
						}
						
						// Check if an update is requested
						if (action != null && action.equals("update")) {
							// Begin transaction
	                        conn.setAutoCommit(false);
	                        
	                        // Create the prepared statement and use it to
	                        // UPDATE the course attributes in the course table.
	                        PreparedStatement pstmt = conn.prepareStatement(
	                            "UPDATE review_session SET date_time = ? WHERE review_session_id = ?");
							pstmt.setString(1, request.getParameter("date_time")); 		
							pstmt.setInt(2, Integer.parseInt(request.getParameter("review_session_id")));   
							
	                        pstmt.executeUpdate();
	                        // Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
						}
						
						if (action != null && action.equals("delete")) {
							// Begin transaction
	                        conn.setAutoCommit(false);
	                        PreparedStatement pstmt = conn.prepareStatement(
		                            "DELETE FROM review_session WHERE review_session_id = ?");
	                        pstmt.setInt(1, Integer.parseInt(request.getParameter("review_session_id"))); 
	                        pstmt.executeUpdate();
	                        
	                        conn.setAutoCommit(true);
						}					
						// Create the statement
						Statement statement = conn.createStatement();
						
						//obtain all the section information 
						ResultSet rsSection = statement.executeQuery("SELECT * FROM section ORDER BY section_id");
						CachedRowSet cachedSection = new CachedRowSetImpl();
						cachedSection.populate(rsSection);	
					
						//obtain the current section information	
						ResultSet rsReview = statement.executeQuery("SELECT * FROM review_session");
						CachedRowSet cachedReview = new CachedRowSetImpl();
						cachedReview.populate(rsReview);
						
				%>
				<table border="1">
					<tr>					
						<th>Class</th>			
						<th>Date and Time</th>										
					</tr>
					<tr>
						<form action="review_session.jsp" method="POST">
							<input type="hidden" value="insert" name="action">
							<td> 
								<select name="section_id">
								<%
								while(cachedSection.next()){
									PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM class WHERE class_id=?");
									pstmt.setInt(1,cachedSection.getInt("class_id"));
									ResultSet rsClass = pstmt.executeQuery();
									if(rsClass.next()){
									String course_number = rsClass.getString("course_number");
									String title = rsClass.getString("title");
									String quarter = rsClass.getString("quarter");
									int year = rsClass.getInt("year");
								%>
								<option value="<%=cachedSection.getInt("section_id")%>">
									Section_ID:<%=cachedSection.getInt("section_id")%> <%=course_number%> <%=title%>(<%=quarter%> <%=year%>)
								</option>
									<% 
									}
									%>	
								<%
								}
								%>	
								</select>
							</td>	
							
							<td><input type="text" value="" name="date_time" size="20"></td>		

							<td><input type="submit" value="Insert"></td>
						</form>
					</tr>
						
							<% 
							
                     			while(cachedReview.next()){		
                         	%> 
                         	<tr>	
                     			<form action="review_session.jsp" method="POST">
                        		<input type="hidden" value="update" name="action">                        
                         		<input type="hidden" value="<%=cachedReview.getInt("review_session_id")%>" name="review_session_id">
                         		
                         		<td>
                         		                      		
              					<%
									PreparedStatement pstmt = conn.prepareStatement("SELECT class_id FROM section WHERE section_id=?");
									pstmt.setInt(1,cachedReview.getInt("section_id"));
									ResultSet rs = pstmt.executeQuery();
									rs.next();
									pstmt = conn.prepareStatement("SELECT * FROM class WHERE class_id=?");
									pstmt.setInt(1,rs.getInt("class_id"));
									ResultSet rsClass = pstmt.executeQuery();
									rsClass.next();
									String course_number = rsClass.getString("course_number");
									String title = rsClass.getString("title");
									String quarter = rsClass.getString("quarter");
									int year = rsClass.getInt("year");								
								%>
									
									Section_ID:<%=cachedReview.getInt("section_id")%> <%=course_number%> <%=title%>(<%=quarter%> <%=year%>)

                            	</td>
								<td><input type="text" value="<%=cachedReview.getString("date_time")%>" name="date_time" size="20"></td>                          	
                                <td><input type="submit" value="Update"></td>
                            			
                        		</form>
                     				
                        		<td>
                       				<form action="review_session.jsp" method="POST">
                           				<input type="hidden" value="delete" name="action">
                          				<input type="hidden" value="<%= cachedReview.getInt("review_session_id") %>" name="review_session_id">                       			
                               			<input type="submit" value="Delete">
                           			</form>
                           		</td>
                       
                    		</tr>
                    			<% 
                            	}
                            	%>
					
					<%
						} catch (SQLException e) {
							System.out.println(e.getMessage());
							//conn.rollback();
						} catch (Exception e) {
							System.out.println(e.getMessage());
							//conn.rollback();
						}
					%>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>



